from flask import Flask, render_template, request, redirect, url_for, jsonify
import sqlite3
from datetime import datetime
import requests
app = Flask(__name__)

# 连接到 SQLite 数据库
def connect_db():
    conn = sqlite3.connect('test_data.db')
    return conn

# 创建数据表
def create_table():
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute('''CREATE TABLE IF NOT EXISTS test_data (
                      id INTEGER PRIMARY KEY,
                      test_date TEXT,
                      test_datetime TEXT,
                      water_level INTEGER,
                      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)''')
    conn.commit()
    conn.close()

# 插入测试数据
def insert_data(test_date, test_datetime, water_level):
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute('''INSERT INTO test_data (test_date, test_datetime, water_level)
                      VALUES (?, ?, ?)''', (test_date, test_datetime, water_level))
    conn.commit()
    conn.close()


# 提交测试数据
@app.route('/submit', methods=['POST'])
def submit():
    test_date = request.form['test_date']
    test_datetime = f"{test_date} {request.form['test_time']}"
    water_level = request.form['water_level']
    insert_data(test_date, test_datetime, water_level)
    return redirect(url_for('index'))

# 删除测试数据
@app.route('/delete/<int:id>', methods=['POST'])
def delete(id):
    delete_data(id)
    return redirect(url_for('index'))

# 删除测试数据
def delete_data(id):
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute('''DELETE FROM test_data WHERE id = ?''', (id,))
    conn.commit()
    conn.close()

# 获取测试数据并按创建时间降序排序
def get_data_by_date_range(start_date, end_date, page, per_page):
    conn = connect_db()
    cursor = conn.cursor()
    offset = (page - 1) * per_page
    cursor.execute('''SELECT * FROM test_data WHERE test_date BETWEEN ? AND ? 
                      ORDER BY created_at DESC LIMIT ? OFFSET ?''',
                   (start_date, end_date, per_page, offset))
    data = cursor.fetchall()
    cursor.execute('''SELECT COUNT(*) FROM test_data WHERE test_date BETWEEN ? AND ?''', (start_date, end_date))
    total = cursor.fetchone()[0]
    conn.close()
    return data, total

# 获取最新的100条test_datetime和water_level数据
def get_latest_100_data():
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute('''SELECT test_datetime, water_level FROM test_data 
                      ORDER BY created_at DESC LIMIT 100''')
    data = cursor.fetchall()
    conn.close()
    return data

# 首页，显示所有测试数据
@app.route('/')
def index():
    today = datetime.today().strftime('%Y-%m-%d')
    current_time = datetime.now().strftime('%H:%M')
    start_date = request.args.get('start_date', today)
    end_date = request.args.get('end_date', today)
    page = int(request.args.get('page', 1))
    per_page = 5  # 每页显示5条记录
    data, total = get_data_by_date_range(start_date, end_date, page, per_page)
    total_pages = (total + per_page - 1) // per_page
    # 增加序号字段
    data_with_index = [(i + 1 + (page - 1) * per_page, *row) for i, row in enumerate(data)]
    return render_template('index.html', title="饮水机数据记录", data=data_with_index, today=today, current_time=current_time, start_date=start_date, end_date=end_date, page=page, total_pages=total_pages)

# 数据预测页面
@app.route('/predict')
def predict():
    return render_template('predict.html', title="猜一猜")

# 数据预测结果处理
@app.route('/predict_result', methods=['POST'])
def predict_result():
    # 获取最新的100条数据作为提示词
    latest_data = get_latest_100_data()
    prompt = "以下是最近期办公室饮水机时间和剩余热水水量的数据：---\n "
    for entry in latest_data:
        prompt += f"时间: {entry[0]}, 热水水量: {entry[1]}%\n"

    prompt += " 请总结规律并帮我预测时间是："+datetime.now().strftime("%Y-%m-%d %H:%M") \
              + "的热水水量是多少？你需要严格按照 '预测结果是：当前水量是XXX' 的格式回答，千万不要做任何解释"

    # 定义要发送的 JSON 数据
    data = {
        "model": "llama3-chinese:8b",
        "messages": [
            {
                "role": "system",
                "content": "你是一个高冷的规律总结大师，可以根据提供的数据进行预测结果,不需要做成任何解释"
            },
            {
                "role": "user",
                "content": prompt
            }
        ],
        "options": {
            "temperature": 0.5
        },
        "stream": False
    }

    # 定义目标 URL
    url = "http://192.168.10.70:11434/api/chat"

    print("prompt:"+prompt)
    # 发送 POST 请求，并将 JSON 数据作为请求体发送
    response = requests.post(url, json=data)

    return render_template('predict.html', title="猜一猜", prediction=response.json()['message']['content'])

if __name__ == '__main__':
    create_table()
    app.run(host='0.0.0.0', debug=True)
